package cn.ctbu.xiaopeng.studentscoremanage.dao;


import cn.ctbu.xiaopeng.studentscoremanage.pojo.dto.CourseQueryDTO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.entity.Course;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.CoursePageVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author xiaopeng
 * @version 1.0
 */
public interface CourseRepository extends JpaRepository<Course, Integer> {


    /**
     * 通过tno将课程表和教师表连接起来，查询课程和教师信息
     * 使用 @Query 注解进行自定义查询，分页查询课程和教师信息并返回 CoursePageVO
     *
     * @param courseQueryDTO
     * @param pageable
     * @return
     */
    @Query("SELECT new cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.CoursePageVO(" +
            "c.id, c.courseNo, c.tno,t.name, c.cname, c.credits, c.hours, " +
            "c.departmentId,dep.department, c.semester, c.assessMethod) " +
            "FROM tb_course c " +
            "JOIN tb_teacher t ON c.tno = t.tno " +
            "JOIN tb_department dep ON c.departmentId = dep.departmentId " +
            "WHERE (:#{#courseQueryDTO.tname} IS NULL OR t.name LIKE %:#{#courseQueryDTO.tname}%) " +
            "AND (:#{#courseQueryDTO.cname} IS NULL OR c.cname LIKE %:#{#courseQueryDTO.cname}%) " +
            "AND (:#{#courseQueryDTO.semester} IS NULL OR c.semester LIKE %:#{#courseQueryDTO.semester}%) " +
            "AND (:#{#courseQueryDTO.department} IS NULL OR dep.department LIKE %:#{#courseQueryDTO.department}%) " +
            "AND (:#{#courseQueryDTO.courseNo} IS NULL OR c.courseNo LIKE %:#{#courseQueryDTO.courseNo}%) " +
            "AND (:#{#courseQueryDTO.credits} IS NULL OR c.credits = :#{#courseQueryDTO.credits}) " +
            "AND (:#{#courseQueryDTO.hours} IS NULL OR c.hours = :#{#courseQueryDTO.hours}) " +
            "AND (:#{#courseQueryDTO.assessMethod} IS NULL OR c.assessMethod LIKE %:#{#courseQueryDTO.assessMethod}%)")
    Page<CoursePageVO> getCoursePageByConditions(@Param("courseQueryDTO") CourseQueryDTO courseQueryDTO, Pageable pageable);


    /**
     * 根据课程名称模糊查询
     *
     * @param cname
     * @return
     */
    List<Course> findByCnameLike(String cname);

    /**
     * 根据课程编号查询
     *
     * @param cno
     * @return
     */
    Course findByCourseNo(String cno);

    /**
     * 根据课程编号删除
     *
     * @param cno
     * @return
     */
    void deleteByCourseNo(String cno);

    /**
     * 根据id批量删除
     *
     * @param ids
     */
    @Modifying
    @Query("DELETE FROM tb_course c WHERE c.id IN :ids")
    void deleteByIds(List<Integer> ids);


    /**
     * 根据课程模糊查询 (startingWith)
     *
     * @param name
     * @return
     */
    List<Course> findByCnameStartingWith(String name);


    /**
     * 根据课程编号更新课程人数
     * @param cno
     * @param cNums
     * @return
     */
/*    @Modifying
    @Transactional
    @Query("UPDATE course c SET c.nums = :cNums WHERE c.cno = :cno")
    void updateCNums(@Param("cno") String cno,@Param("cNums") Integer cNums);*/


}
